﻿using OfficeOpenXml;
using System;
using System.Data;
using System.Data.OleDb;
using System.IO;
using System.Linq;
using System.Windows.Forms;

namespace socket_client
{
    class DatabaseManager : IDisposable
    {

        // 数据库连接字符串示例："Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\\mydb.mdb;"
        private readonly string _connString;
        private OleDbConnection _conn;

        /// <summary>
        /// 初始化数据库帮助类
        /// </summary>
        /// <param name="dbPath">数据库文件路径，例如：C:\test.mdb</param>
        public DatabaseManager(string dbPath, string password)
        {
            // 针对 .accdb 文件
            _connString = $"Provider=Microsoft.ACE.OLEDB.12.0;Data Source={dbPath};Jet OLEDB:Database Password={password};";

        }

        /// <summary>
        /// 执行增删改操作（返回受影响行数）
        /// </summary>
        public int RunSql(string sql, params object[] parameters)
        {
            using (var cmd = new OleDbCommand(sql, GetConnection()))
            {
                // 添加参数防止SQL注入
                foreach (var p in parameters)
                {
                    cmd.Parameters.AddWithValue("?", p ?? DBNull.Value);
                }
                return cmd.ExecuteNonQuery();
            }
        }

        /// <summary>
        /// 执行查询（返回数据表格）
        /// </summary>
        public DataTable GetTable(string sql, params object[] parameters)
        {
            var table = new DataTable();
            using (var cmd = new OleDbCommand(sql, GetConnection()))
            {
                foreach (var p in parameters)
                {
                    cmd.Parameters.AddWithValue("?", p ?? DBNull.Value);
                }

                using (var reader = cmd.ExecuteReader())
                {
                    table.Load(reader);
                }
            }
            return table;
        }

        /// <summary>
        /// 获取数据库连接（自动打开）
        /// </summary>
        public OleDbConnection GetConnection()
        {
            try
            {
                using (OleDbConnection conn = new OleDbConnection(_connString))
                {
                    conn.Open();
                    AsyncFileLogger.WriteLog("数据库连接成功！");
                }
            }
            catch (OleDbException ex)
            {
                Console.WriteLine($"数据库错误: {ex.Message}");
                if (ex.Message.Contains("不可识别的数据库格式") || ex.Message.Contains("不是有效的密码"))
                {
                    AsyncFileLogger.WriteLog("可能原因：密码错误或驱动未安装！");
                }
            }

            if (_conn == null)
            {
                _conn = new OleDbConnection(_connString);
                _conn.Open();
            }
            return _conn;
        }

        /// <summary>
        /// 关闭连接释放资源
        /// </summary>
        public void Dispose()
        {
            if (_conn != null)
            {
                _conn.Close();
                _conn.Dispose();
                _conn = null;
            }
        }

        public void saveExcel(DataTable data, string tableName, string excelPath)
        {
            // 导出到 Excel

            using (var package = new ExcelPackage(new FileInfo(excelPath)))
            {

                // 检查是否存在同名工作表，存在则删除
                if (package.Workbook.Worksheets.Any(sheet => sheet.Name == tableName))
                {
                    package.Workbook.Worksheets.Delete(tableName);
                }
                var worksheet = package.Workbook.Worksheets.Add(tableName);
                worksheet.Cells["A1"].LoadFromDataTable(data, true); // 包含列头
                worksheet.Cells.AutoFitColumns();
                package.Save();
                AsyncFileLogger.WriteLog($"DB导出excle成功！{excelPath}");
            }
        }
        internal void OleDbConnection()
        {
            throw new NotImplementedException();
        }
    }
}
